# -*- coding: utf-8 -*-
import os
import re
import MySQLdb
from lib.__util__ import Util
import json
from lib.__date__ import Date
from lib.__log__ import Log
class Sql:
     __obj__ = False
     dbName = ""
     tableName = ""
     __connection__ = ""
     config_context = ""
     where = ""
     field = "*"
     limit = ""
     order = ""
     __sqlstr__ = ""
     __lastSql__ = ""
     def __init__(self):
         Sql.__obj__ = self
         self.dbName = ""
         self.field = "*"
         self.config_context  = ""
         self.tableName = ""
         self.__connection__ = ""
         self.where = ""
         self.order  = ""
         self.limit = ""
         self.__sqlstr__= ""
         self.cur = False
         self.datas = ""
     @staticmethod
     def getInstance():
         if(Sql.__obj__):
             return Sql.__obj__
         else:
             return Sql()
     
     def LoadConfig(self, path):
         souccess =  Util.file_put_getContents(path)
         self.config_context  =  json.loads(souccess)
         return self.__obj__
     def Db(self,dbName = ""):
          self.dbName = dbName
          return self.__obj__
     def Table(self, tableName = ""):
          self.tableName = tableName
          return self.__obj__
     def connection(self):
         hosts = "localhost"
         if "host" in self.config_context[0]:
            hosts = self.config_context[0]['host']
         self.__connection__ = MySQLdb.connect(user=self.config_context[0]['database_user'],passwd=self.config_context[0]['database_password'],host=hosts, charset="utf8")
         return self.__connection__.cursor(cursorclass = MySQLdb.cursors.DictCursor)
     
     def Fields(self,field = "*"):
         if(type(field) == list) :
             self.field = ""
             for index in range(0,len(field)) :
                 self.field = self.field + field[index] + ","
                 self.field =  self.field[0:len(self.field)-1]
         else:
           if "" == field : 
               self.field = "*"
           else: 
               self.field = field
         return self.__obj__
     
     def Where(self, whereSql=""):
          if("" == whereSql):
              self.where = ""
          else:
              self.where = self.where + " WHERE "+ whereSql + " "
          return self.__obj__
     
     def Limit(self,start = 0, end = 0):
         if(end == 0):
             self.limit = " LIMIT 0,"+ str(start) + " "
         else:
             self.limit = " LIMIT "+str(start)+","+ str(end) + " "
         return self.__obj__
     def getLastSql(self):
         return self.__lastSql__
     
     def Order(self, order=""):
         if("" == order):
             self.order = ""
         else:
             self.order = " ORDER " + order
             return self.__obj__
     def Select(self):
         self.__selectsql__ = "";
         if  (("" == self.dbName) | ("" == self.tableName)):
             return  False
         else:
             self.__sqlstr__ = "SELECT "+self.field+" FROM "+ self.dbName +"." + self.tableName + self.where + self.limit + self.order                         
             self.__lastSql__ = self.__sqlstr__
             self.cur = self.connection()
             self.__connection__.select_db(self.dbName)
             self.cur.execute(self.__sqlstr__)
             self.ClearExcute()
             return self.cur.fetchall()
     def Close(self):
         self.cur.close()
     def setInsertData(self,datas = ""):
          key = ""
          value = ""
          if type(datas) == dict:
              for tem in datas:
                  key = key + tem + ","
                  value = value + "'"+datas[tem] + "',"
          key = " (" + key[0:len(key)-1] + ")"
          value = "(" + value[0:len(value)-1] + ") "
          self.datas =   key +"VALUES" + value
          return self.__obj__
     def Insert(self):
         self.__sqlstr__ = "INSERT INTO " + self.tableName +  self.datas
         self.__lastSql__ = self.__sqlstr__
         self.cur = self.connection()
         self.__connection__.select_db(self.dbName)
         self.cur.execute(self.__sqlstr__)
         self.ClearExcute()
     def Update(self,datas=""):
            value =""
            if type(datas) == dict:
                for tem in datas:
                     value = value + tem + "='" + datas[tem] + "',"
                self.__sqlstr__ = "UPDATE "+self.dbName+"."+self.tableName+" SET "  + str(value[0:len(value) - 1]) +" "+self.where
               
                self.__lastSql__ = self.__sqlstr__
                self.cur = self.connection()
                self.__connection__.select_db(self.dbName)
                self.cur.execute(self.__sqlstr__)
                self.ClearExcute()
     def ClearExcute(self):
         self.__connection__ = ""
         self.dbName = ""
         self.field = "*"
         self.tableName = ""
         self.limit = ""
         self.order = ""
         self.where = ""
         self.__sqlstr__ = ""
         self.datas =""
     def backupStart(self):
          for index  in range(0,len(self.config_context)):
             self.backup(self.config_context[index])
     def backup(self, config):
         date = Date.getInstance()
         log = Log.getInstance()
         __username__ = config['database_user']
         __password__ = config['database_password']
         __database__ = config['database']
         __logpath__  = "backup.log"
         __host__ = ""
         __tar__ = "{%Y%m%d%H%I%S}.tar.gz"
         __backpath__ = ""
         if "tar" in config:
             __tar__ = config['tar']
         if "host" in config:
             __host__ = " -h " + config['host']
         if "back_path" in config:
             __backpath__ = config['back_path']
         if "logpath" in config:
             __logpath__ = config['logpath']
         for index  in range(0,len(__database__)):
             for key in __database__[index]:
                
                 if ("log" != key):
                    fileName = __database__[index][key]
                    text = re.findall("\{(.*)\}",fileName)
                    if 1 == len(text):
                       back_date = date.getDate(text[0])
                       fileName =  fileName.replace("{"+text[0]+"}", back_date)
                    
                    command  = "mysqldump "+__host__+" -u"+__username__+" -p"+__password__+" "+key +" > "+__backpath__+fileName;
                    os.system(command)
                    if "log" in __database__[index]:
                         if(__database__[index]['log'] == True):
                             logComand = "["+ date.getDate("%Y-%m-%d %H:%I:%S") + "] " + key + " backup to " + __backpath__+fileName;
                             log.write(__logpath__, logComand)
                    else:
                           logComand = "["+ date.getDate("%Y-%m-%d %H:%I:%S") + "] " + key + " backup to " + __backpath__+fileName;
                           log.write(__logpath__, logComand)           
         if __tar__:
            __tartime__ = re.findall("\{(.*)\}",__tar__)
            if 1 == len(__tartime__):
                   __tarrplate__ = date.getDate(__tartime__[0])
                   __tarfileName__ =  __tar__.replace("{"+__tartime__[0]+"}", __tarrplate__)
            else:
                __tarfileName__ = __tar__
            os.system("tar zcvf "+__backpath__+__tarfileName__+" "+__backpath__+"*.sql");
            os.system("rm "+__backpath__+"*.sql");
            log.write(__logpath__, "["+ date.getDate("%Y-%m-%d %H:%I:%S") + "] create "+__backpath__+__tarfileName__)
            